'''
Author: devis.dong
Email: devis.dong@gmail.com
Date: 2022-10-19 01:05:58
LastEditTime: 2023-01-04 17:21:21
LastEditors: devis.dong
FilePath: \server\sqldb.py
Description:
'''

from myutils import *
# import mysql.connector
import logging
import sqlite3   #导入sqlite3模块
import threading

class DBMysql():
  # def __init__(self, host="localhost", user="root", passwd="123456", database="test3d") -> None:
  #   self.db = mysql.connector.connect(host=host, user=user, passwd=passwd, database=database,auth_plugin='mysql_native_password')
  #   self.cur = self.db.cursor()
  #   logging.info('connect to database %s:%s successfully.' % (host, database))

  def __init__(self,name = r"test3.db") -> None:
    self.db = sqlite3.connect(name, timeout=10,check_same_thread=False)
    self.cur = self.db.cursor()
    self.lock = threading.Lock()
    logging.info('connect to database %s successfully.' % (name))

  def execute(self, cmd):
    status=None
    results = None
    try:
        self.lock.acquire(True)
        logging.info('executing sql cmd=%s, ...' % (cmd))
        #执行sql语句
        self.cur.execute(cmd)
        #获取执行结果
        results = self.cur.fetchall()
        #提交到数据库中
        self.db.commit()
        logging.info('execute sql cmd=%s, successfully.' % cmd)
    except Exception as e:
        #捕获异常
        status=str(e)
        logging.error(e)
        self.lock.release()
    finally:
       self.lock.release()
    #     self.db.close()  # 关闭连接
    return status, results

  def exemany(self, cmd, val):
    status = None
    results = None
    try:
        self.lock.acquire(True)
        logging.info('executing sql (cmd=%s), ...' % (cmd))
        #执行sql语句
        self.cur.executemany(cmd, val)
        #获取执行结果
        results = self.cur.fetchall()
        #提交到数据库中
        self.db.commit()
        logging.info('execute sql (cmd=%s), successfully.' % (cmd))
    except Exception as e:
        #捕获异常
        status=str(e)
        logging.error(e)
        self.lock.release()
    finally:
       self.lock.release()
    #     self.db.close()  # 关闭连接
    return status, results

  def sqlexe(self, cmd, val=None):
    if val is None:
      return self.execute(cmd)
    else:
      return self.exemany(cmd, val)





